﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Data;
using System.Data.Common;

namespace QLHPHP
{
    /// <summary>
    /// Class ve sinh vien
    /// </summary>

    public static class SinhVien
    {
        /// <summary>
        /// Lay danh sach sinh vien
        /// </summary> 
        /// <returns></returns>
        public static DataTable LayDSSinhVien()
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetStudentList";
            return DataAccess.ExecuteSelectCommand(comm);
        }

         
        /// <summary>
        /// Lay sinh vien trong 1 nganh
        /// </summary>
        /// <param name="branchId">Ma nganh</param>
        /// <returns></returns>
        public static DataTable LaySVThuocNganh(int branchId)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetStudentByBranch";

            DbParameter param = comm.CreateParameter();
            param.DbType = DbType.Int16;
            param.ParameterName = "@MaNganh";
            param.Value = branchId;
            comm.Parameters.Add(param);

            return DataAccess.ExecuteSelectCommand(comm);
        }

        /// <summary>
        /// Lay ten sinh vien tu mssv
        /// </summary>
        public static DataTable LayTenSVTuMSSV(int MSSV)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetNameStudentById";

            DbParameter param = comm.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@MSSV";
            param.Value = MSSV;
            comm.Parameters.Add(param);

            return DataAccess.ExecuteSelectCommand(comm);
        }
        
        /// <summary>
        /// Lay tat ca danh sach sinh vien thuoc doi tuong
        /// </summary>
        /// <param name="objectId">Ma doi tuong</param>
        /// <returns></returns>
        public static DataTable LaySVThuocDoiTuong(string objectId)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetStudentByObject";

            DbParameter param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@MaDoiTuong";
            param.Value = objectId;
            comm.Parameters.Add(param);

            return DataAccess.ExecuteSelectCommand(comm);
        }

        /// <summary>
        /// Lay chi tiet sinh vien theo ma sinh vien do
        /// </summary>
        /// <param name="studentId"></param>
        /// <returns></returns>
        public static DataTable LaySVThuocMaSo(string studentId)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "GetStudentById";

            DbParameter param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@MSSV";
            param.Value = studentId;
            comm.Parameters.Add(param);

            return DataAccess.ExecuteSelectCommand(comm);
        }

        /// <summary>
        /// Xoa 1 sinh vien 
        /// </summary>
        /// <param name="studentId">Ma so sinh vien can xoa</param>
        /// <returns></returns>
        public static bool XoaSinhVien(string studentId)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "DeleteStudent";

            DbParameter param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@MSSV";
            param.Value = studentId;
            comm.Parameters.Add(param);

            int result = DataAccess.ExecuteNonQuery(comm);
            return (result != -1);
        }

        /// <summary>
        /// Cap nhat thong tin sinh vien
        /// </summary>
        /// <param name="studentId">Ma so sinh vien</param>
        /// <param name="studentName">Ho ten sinh vien</param>
        /// <param name="birthdate">Ngay sinh</param>
        /// <param name="sex">Gioi tinh</param>
        /// <param name="districtId">Ma huyen</param>
        /// <param name="objectId">Ma doi tuong</param>
        /// <param name="branchId">Ma nganh hoc</param>
        /// <returns></returns>
        public static bool CapNhatSinhVien(int studentId,string studentName, DateTime birthdate, 
            string sex, string districtId, string objectId, string branchId, string PhoneNum, string Address, string Email )
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "UpdateStudent";

            DbParameter param = comm.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@MSSV";
            param.Value = studentId;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@HoTen";
            param.Value = studentName;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.DateTime;
            param.ParameterName = "@NgaySinh";
            param.Value = birthdate;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@GioiTinh";
            param.Value = sex;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@MaHuyen";
            param.Value = districtId;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@MaDoiTuong";
            param.Value = objectId;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@MaNganh";
            param.Value = branchId;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@SoDT";
            param.Value = PhoneNum;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@DiaChi";
            param.Value = Address;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@Email";
            param.Value = Email;
            comm.Parameters.Add(param);

            int result = DataAccess.ExecuteNonQuery(comm);
            return (result != -1);    
        }

        /// <summary>
        /// Them sinh vien
        /// </summary>
        /// <param name="studentId">Ma so sinh vien</param>
        /// <param name="studentName">Ho ten sinh vien</param>
        /// <param name="birthdate">Ngay sinh</param>
        /// <param name="sex">Gioi tinh</param>
        /// <param name="districtId">Ma huyen</param>
        /// <param name="objectId">Ma doi tuong</param>
        /// <param name="branchId">Ma nganh hoc</param>
        /// <returns></returns>
        public static bool ThemSinhVien(string studentName,
            DateTime birthdate, string sex, string districtId, string objectId, string branchId, string PhoneNum, string Address, string Email)
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "CreateStudent";

            DbParameter param = comm.CreateParameter();
            /*param.DbType = DbType.String;
            param.ParameterName = "@MSSV";
            param.Value = studentId;
            comm.Parameters.Add(param);*/

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@HoTen";
            param.Value = studentName;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.DateTime;
            param.ParameterName = "@NgaySinh";
            param.Value = birthdate;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@GioiTinh";
            param.Value = sex;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@MaHuyen";
            param.Value = districtId;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@MaDoiTuong";
            param.Value = objectId;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@MaNganh";
            param.Value = branchId;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@SoDT";
            param.Value = PhoneNum;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@DiaChi";
            param.Value = Address;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.DbType = DbType.String;
            param.ParameterName = "@Email";
            param.Value = Email;
            comm.Parameters.Add(param);

            int result = DataAccess.ExecuteNonQuery(comm);
            return (result != -1);
        }

        /// <summary>
        /// Lay tong sinh vien
        /// </summary>
        public static DataTable TongSinhVien()
        {
            DbCommand comm = DataAccess.CreateCommand();
            comm.CommandText = "TongSinhVien";

            return DataAccess.ExecuteSelectCommand(comm);
        }

    }
}
